﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace TeamDoer.Models
{
	public class TaskList
	{
		public int TaskListId {get;set;}
		public string Name {get;set;}
	}

	public class TaskListsGridModel
	{
		public List<TaskList> TaskLists { get; private set; }
		public TasksGridModel SelectedListTasks { get; set; }

		private readonly TaskService _taskService;

		public TaskListsGridModel(TaskService taskService)
		{
			_taskService = taskService;
			TaskLists = _taskService.GetAllTaskLists();
			SelectedListTasks = null;
		}
	}

	public class TaskInGrid
	{
		public int TaskId { get; set; }
		public string Subject { get; set; }
		public string Body { get; set; }
		public bool IsClosed { get; set; }
	}

	public class TasksGridModel
	{
		public List<TaskInGrid> Tasks { get; private set; }

		public int TaskListId { get; private set; }
		public string TaskListName { get; private set; }
		public string TaskListOwnerName { get; private set; }
		public TaskDetailsModel SelectedTaskDetails { get; set; }

		readonly TaskService _taskSrv;

		public TasksGridModel(TaskService taskService, int taskListId)
		{
			_taskSrv = taskService;

			Tasks = _taskSrv.GetTasksInList(taskListId);
			TaskListId = taskListId;

			string name, ownerName;
			_taskSrv.GetTaskListInfoFromDB(taskListId, out name, out ownerName);
			TaskListName = name;
			TaskListOwnerName = ownerName;

			SelectedTaskDetails = null;
		}
	}

	public class TaskDetailsModel
	{
		public int TaskId { get; private set; }
		public string Subject { get; private set; }
		public string Body { get; private set; }
		public bool IsClosed { get; private set; }
		public DateTime? DueDate { get; private set; }
		public string AssignedToName { get; private set; }
		public string FromName { get; private set; }
		public int TaskListId { get; private set; }
		public bool IsFromPremiumUser { get; private set; }

		public CommentsGridModel CommentsModel { get; private set; }
		public TaskFollowersModel FollowersModel { get; private set; }

		private readonly TaskService _taskService;

		public TaskDetailsModel(TaskService taskService, int taskId)
		{
			TaskId = taskId;

			_taskService = taskService;

			string subject, body, assignedToName, fromName;
			DateTime? dueDate; bool isClosed; int taskListId; bool isFromPremiumUser;
			_taskService.GetAllTaskInfoFromDB(taskId, out subject, out body, out dueDate, out isClosed, out assignedToName, out fromName, out taskListId, out isFromPremiumUser);
			Subject = subject;
			Body = body;
			DueDate = dueDate;
			IsClosed = isClosed;
			AssignedToName = assignedToName;
			FromName = fromName;
			TaskListId = taskListId;
			IsFromPremiumUser = isFromPremiumUser;

			CommentsModel = new CommentsGridModel(taskService, taskId);
			FollowersModel = new TaskFollowersModel(taskService, taskId);
		}
	}

	public class ShareTaskListModel
	{
		public string TaskListName { get; private set; }
		public int TaskListId { get; private set; }

		public List<TaskUser> SharedUserNames { get; private set; }

		private readonly TaskService _taskService;

		public ShareTaskListModel(TaskService taskService, int taskListId)
		{
			_taskService = taskService;
			TaskListId = taskListId;
			TaskListName = _taskService.GetListNameById(taskListId);
			SharedUserNames = _taskService.GetTaskListSharedUsers(taskListId);
		}
	}

	public class Comment
	{
		public string Text { get; set; }
		public int CommentId { get; set; }
		public string FromUserName { get; set; }
		public DateTime Date { get; set; }
		public string AttachedFileName { get; set; }
		public int AttachedFileId { get; set; }
	}

	public class CommentsGridModel
	{
		public int TaskId { get; private set; }
		public List<Comment> CommentsList { get; private set; }

		private readonly TaskService _taskSrv;

		public CommentsGridModel(TaskService taskService, int taskId)
		{
			TaskId = taskId;
			_taskSrv = taskService;
			CommentsList = _taskSrv.GetCommentsFromDB(taskId);
		}
	}

	public class TaskFollowersModel
	{
		public int TaskId { get; private set; }
		public List<TaskUser> FollowersList { get; set; }

		private readonly TaskService _taskSrv;

		public TaskFollowersModel(TaskService taskService, int taskId)
		{
			_taskSrv = taskService;
			TaskId = taskId;
			FollowersList = _taskSrv.GetTaskFollowers(taskId);
		}
	}

	public class TaskUser
	{
		public int UserId { get; private set; }
		public string Email { get; private set; }
		public string FirstName { get; private set; }
		public string LastName { get; private set; }
		public string UserName { get; private set; }
		public string DisplayName { get; private set; }

		public TaskUser(int userId, string username, string email, string firstname, string lastname)
		{
			UserId = userId;
			Email = email;
			UserName = username;
			FirstName = firstname;
			LastName = lastname;
			DisplayName = Helpers.Utils.GetUserFullName(username, firstname, lastname);
		}
	}

	public class TaskService : BaseService
	{
		private readonly int _currentUserId;
		private readonly string _currentUserName;

		public TaskService(string currentUsername)
		{
			AccountService srv = new AccountService();
			_currentUserId = srv.GetUserIdByUserName(currentUsername).Value;
			_currentUserName = currentUsername;
		}

		public int AddComment(string text, int taskId, HttpFileCollectionBase attachedFiles)
		{
			if (string.IsNullOrWhiteSpace(text)) return 0;

			//ading comment
			_cmd.Parameters.Clear();
			_cmd.CommandText = @"INSERT INTO Comments (TaskID, UserID, Text)
				VALUES (@TaskID, @UserID, @Text)
				SELECT @@IDENTITY";
			_cmd.Parameters.AddWithValue("@UserID", _currentUserId);
			_cmd.Parameters.AddWithValue("@TaskID", taskId);
			_cmd.Parameters.AddWithValue("@Text", text);
			_cn.Open();
			int commentId = Convert.ToInt32(_cmd.ExecuteScalar());
			_cn.Close();

			AddTaskFollower(taskId, _currentUserId);

			SendMesageToTaskFollowers(taskId, string.Format("The task you are following has been updated by {0}.\n\n{1}", _currentUserName, text), true);

			//attaching files if any
			if (attachedFiles.Count > 0)
				AttachFilesToComment(commentId, taskId, attachedFiles);

			return commentId;
		}

		public void GetFileById(int fileId, out string filename, out string mimeType, out byte[] fileData)
		{
			filename = mimeType = null;
			fileData = null;

			_cmd.Parameters.Clear();
			_cmd.CommandText = @"SELECT FileName, FileContent, TaskID, CommentID, MimeType
				FROM Files
				WHERE FileID=@FileID";
			_cmd.Parameters.AddWithValue("@FileID", fileId);
			_cn.Open();
			SqlDataReader dr = _cmd.ExecuteReader();
			if (dr.Read())
			{
				filename = dr["FileName"].ToString();
				mimeType = dr["MimeType"].ToString();
				fileData = (byte[]) dr["FileContent"];
			}
			_cn.Close();
		}

		private void AttachFilesToComment(int commentId, int taskId, HttpFileCollectionBase attachedFiles)
		{
			if (attachedFiles.Count == 0) return;
			if (attachedFiles[0] == null) return;
			if (string.IsNullOrWhiteSpace(attachedFiles[0].FileName)) return;

			string fileName = attachedFiles[0].FileName.Substring(attachedFiles[0].FileName.LastIndexOf("\\") + 1);
			int fileLen = attachedFiles[0].ContentLength;
			byte[] fileData = new byte[fileLen];
			attachedFiles[0].InputStream.Read(fileData, 0, fileLen);

			//ading file
			_cmd.Parameters.Clear();
			_cmd.CommandText = @"INSERT INTO Files (FileName, FileContent, TaskID, CommentID, MimeType)
				VALUES (@FileName, @FileContent, @TaskID, @CommentID, @MimeType)";
			_cmd.Parameters.AddWithValue("@FileName", fileName);
			_cmd.Parameters.AddWithValue("@FileContent", fileData);
			_cmd.Parameters.AddWithValue("@TaskID", taskId);
			_cmd.Parameters.AddWithValue("@CommentID", commentId);
			_cmd.Parameters.AddWithValue("@MimeType", attachedFiles[0].ContentType);
			_cn.Open();
			_cmd.ExecuteNonQuery();
			_cn.Close();
		}

		private void SendMesageToTaskFollowers(int taskId, string messageText, bool excludeCurrentUser)
		{
			string taskSubj, taskBody;
			GetTaskTextFromDB(taskId, out taskSubj, out taskBody);

			//adding link to the task at the end of the message
			messageText += string.Format("\n\nTask: {0}\n\nPlease visit http://teamdoer.com/Tasks/TaskById?taskId={1} to view the update and respond.", taskSubj, taskId);

			foreach (TaskUser follower in GetTaskFollowers(taskId))
			{
				if (excludeCurrentUser && follower.UserId == _currentUserId)
					continue;

				Helpers.Utils.SendEmail(follower.Email, "update from TeamDoer", messageText);
			}
		}

		private void SendMesageToTaskListUsers(int taskListId, string messageText, bool excludeCurrentUser)
		{
			foreach (TaskUser user in GetTaskListSharedUsers(taskListId))
			{
				if (excludeCurrentUser && user.UserId == _currentUserId)
					continue;

				Helpers.Utils.SendEmail(user.Email, "update from TeamDoer", messageText);
			}
		}

		public List<Comment> GetCommentsFromDB(int taskId)
		{
			List<Comment> list = new List<Comment>();

			_cmd.Parameters.Clear();
			_cmd.CommandText = @"SELECT Comments.CommentID, Comments.UserID, Comments.Text, Comments.Date, Users.UserName, Users.FirstName, Users.LastName, Files.FileName, Files.FileID
				FROM Comments
				INNER JOIN Users ON Comments.UserID=Users.UserID
				LEFT JOIN Files ON Comments.CommentID=Files.CommentID
				WHERE Comments.TaskID=@TaskID
				ORDER BY Comments.CommentID DESC";
			_cmd.Parameters.AddWithValue("@TaskID", taskId);
			_cn.Open();
			SqlDataReader dr = _cmd.ExecuteReader();
			while (dr.Read())
			{
				Comment comment = new Comment();
				comment.CommentId = (int)dr["CommentID"];
				comment.FromUserName = Helpers.Utils.GetUserFullName(dr["UserName"], dr["FirstName"], dr["LastName"]);
				comment.Date = (DateTime)dr["Date"];
				comment.Text = dr["Text"].ToString();
				comment.AttachedFileName = dr["FileName"].ToString() == "" ? null : dr["FileName"].ToString();
				comment.AttachedFileId = dr["FileID"].ToString() == "" ? 0 : Convert.ToInt32(dr["FileID"]);
				list.Add(comment);
			}
			dr.Close();
			_cn.Close();

			return list;
		}

		public void GrantAccessToList(int taskListId, int userId)
		{
			_cmd.Parameters.Clear();
			_cmd.CommandText = @"if not exists(SELECT UserID FROM TaskListPermissions WHERE UserID=@UserID AND TaskListID=@TaskListID)
				INSERT INTO TaskListPermissions (UserID, TaskListID) VALUES (@UserID, @TaskListID)";
			_cmd.Parameters.AddWithValue("@UserID", userId);
			_cmd.Parameters.AddWithValue("@TaskListID", taskListId);
			_cn.Open();
			_cmd.ExecuteNonQuery();
			_cn.Close();
		}

		public void CloseTask(int taskId, bool closed)
		{
			_cmd.Parameters.Clear();
			_cmd.CommandText = @"UPDATE Tasks SET IsClosed=@IsClosed WHERE TaskID=@TaskID";
			_cmd.Parameters.AddWithValue("@TaskID", taskId);
			_cmd.Parameters.AddWithValue("@IsClosed", closed);
			_cn.Open();
			_cmd.ExecuteNonQuery();
			_cn.Close();
		}

		public void AssignTask(int taskId, int userId)
		{
			_cmd.Parameters.Clear();
			_cmd.CommandText = @"UPDATE Tasks SET AssigneeID=@AssigneeID WHERE TaskID=@TaskID";
			_cmd.Parameters.AddWithValue("@TaskID", taskId);
			_cmd.Parameters.AddWithValue("@AssigneeID", userId);
			_cn.Open();
			_cmd.ExecuteNonQuery();
			_cn.Close();

			AddTaskFollower(taskId, userId);
		}

		public void ShareListWithEmails(int taskListId, List<string> emails)
		{
			AccountService srv = new AccountService();
			foreach (string email in emails)
			{
				int? userId = srv.GetUserIdByEmail(email);
				string pswMailBody;
				if (userId != null)
				{
					pswMailBody = "Use your existing password.";
				}
				else
				{
					string psw = Helpers.Utils.GenerateRandomCode(6);
					srv.CreateUser(email, psw, email, "", "");
					userId = srv.GetUserIdByEmail(email);
					pswMailBody = string.Format("Username: {0}\nPassword: {1}\n(you can change it later)", email, psw);
				}

				string username, firstname, lastname;
				srv.GetUserNameById(_currentUserId, out username, out firstname, out lastname);
				string curUser = firstname + " " + lastname + " (" + username + ")"; //"John Smith (johnny33)"

				string taskListName = GetListNameById(taskListId);

				//grant access
				GrantAccessToList(taskListId, userId.Value);

				string url = "http://teamdoer.com/Tasks/TasksByListId?taskListId=" + taskListId;
				string emailBody = string.Format("Hi\n\n{0} wants to share the \"{1}\" task-list with you.\nPlease follow this link: {2}\n",
												 curUser, taskListName, url) + pswMailBody;

				Helpers.Utils.SendEmail(email, "A task-list has been shared with you", emailBody);
			}
		}

		public string GetListNameById(int taskListId)
		{
			//getting list name from db
			_cmd.Parameters.Clear();
			_cmd.CommandText = @"SELECT Name FROM TaskLists WHERE TaskListID=@TaskListID";
			_cmd.Parameters.AddWithValue("@TaskListID", taskListId);
			_cn.Open();
			string name = _cmd.ExecuteScalar() as string;
			_cn.Close();

			return name;
		}

		public void UpdateTask(int taskId, string subject, string body)
		{
			_cmd.Parameters.Clear();
			_cmd.CommandText = @"UPDATE Tasks SET Subject=@Subject, Body=@Body WHERE TaskID=@TaskID";
			_cmd.Parameters.AddWithValue("@TaskID", taskId);
			_cmd.Parameters.AddWithValue("@Subject", subject);
			_cmd.Parameters.AddWithValue("@Body", body);
			_cn.Open();
			_cmd.ExecuteNonQuery();
			_cn.Close();
		}

		public void UpdateDueDate(int taskId, DateTime? newDate)
		{
			_cmd.Parameters.Clear();
			_cmd.CommandText = @"UPDATE Tasks SET DueDate=@DueDate WHERE TaskID=@TaskID";
			_cmd.Parameters.AddWithValue("@TaskID", taskId);
			_cmd.Parameters.AddWithValue("@DueDate", newDate.HasValue ? (object) newDate.Value : DBNull.Value);
			_cn.Open();
			_cmd.ExecuteNonQuery();
			_cn.Close();
		}

		private void AddTaskFollower(int taskId, int userId)
		{
			_cmd.Parameters.Clear();
			_cmd.CommandText = @"if not exists (SELECT UserID FROM TaskFollowers WHERE UserID=@UserID AND TaskID=@TaskID)
				INSERT INTO TaskFollowers (TaskID, UserID) VALUES (@TaskID, @UserID)";
			_cmd.Parameters.AddWithValue("@UserID", userId);
			_cmd.Parameters.AddWithValue("@TaskID", taskId);
			_cn.Open();
			_cmd.ExecuteNonQuery();
			_cn.Close();
		}

		public void GetAllTaskInfoFromDB(int taskId, out string subject, out string body, out DateTime? dueDate, out bool isClosed,
			out string assignedToName, out string fromName, out int taskListId, out bool isFromPremiumUser)
		{
			subject = body = assignedToName = fromName = "";
			dueDate = DateTime.MinValue;
			isClosed = false;
			taskListId = 0;
			isFromPremiumUser = false;

			_cmd.Parameters.Clear();
			_cmd.CommandText = @"SELECT Tasks.TaskID, Tasks.Subject, Tasks.Body, Tasks.IsClosed, Tasks.DueDate, Tasks.TaskListID,
					Users_1.UserName as AssUserName, Users_1.FirstName as AssFirstName, Users_1.LastName as AssLastName,
					Users.UserName, Users.FirstName, Users.LastName, Users.IsPremium
				FROM Tasks
				INNER JOIN Users ON Users.UserID=Tasks.UserID
				LEFT JOIN Users Users_1 ON Tasks.AssigneeID=Users_1.UserID
				WHERE TaskID=@TaskID";
			_cmd.Parameters.AddWithValue("@TaskID", taskId);
			_cn.Open();
			SqlDataReader dr = _cmd.ExecuteReader();
			while (dr.Read())
			{
				subject = dr["Subject"].ToString();
				body = dr["Body"].ToString();
				dueDate = dr["DueDate"] is DBNull ? (DateTime?) null : (DateTime) dr["DueDate"];
				isClosed = (bool)dr["IsClosed"];
				assignedToName = Helpers.Utils.GetUserFullName(dr["AssUserName"], dr["AssFirstName"], dr["AssLastName"]);
				fromName = Helpers.Utils.GetUserFullName(dr["UserName"], dr["FirstName"], dr["LastName"]);
				taskListId = Convert.ToInt32(dr["TaskListID"]);
				isFromPremiumUser = (bool)dr["IsPremium"];
			}
			dr.Close();
			_cn.Close();
		}

		private void GetTaskTextFromDB(int taskId, out string subject, out string body)
		{
			subject = body = "";

			_cmd.Parameters.Clear();
			_cmd.CommandText = @"SELECT Tasks.Subject, Tasks.Body
				FROM Tasks
				WHERE TaskID=@TaskID";
			_cmd.Parameters.AddWithValue("@TaskID", taskId);
			_cn.Open();
			SqlDataReader dr = _cmd.ExecuteReader();
			while (dr.Read())
			{
				subject = dr["Subject"].ToString();
				body = dr["Body"].ToString();
			}
			dr.Close();
			_cn.Close();
		}

		public void DeleteTask(int taskId)
		{
			_cmd.Parameters.Clear();
			_cmd.Parameters.AddWithValue("@TaskID", taskId);

			_cn.Open();
			_cmd.CommandText = @"DELETE FROM TaskFollowers WHERE TaskID=@TaskID";
			_cmd.ExecuteNonQuery();

			_cmd.CommandText = @"DELETE FROM Tasks WHERE TaskID=@TaskID";
			_cmd.ExecuteNonQuery();
			_cn.Close();
		}

		public int AddTask(int taskListId, string subject, string body, DateTime? dueDate)
		{
			if (string.IsNullOrWhiteSpace(subject)) return 0;

			_cmd.Parameters.Clear();
			_cmd.CommandText = @"INSERT INTO Tasks (UserID, Subject, Body, DueDate, TaskListID)
				VALUES (@UserID, @Subject, @Body, @DueDate, @TaskListID)
				SELECT @@IDENTITY";
			_cmd.Parameters.AddWithValue("@UserID", _currentUserId);
			_cmd.Parameters.AddWithValue("@TaskListID", taskListId);
			_cmd.Parameters.AddWithValue("@Subject", subject);
			_cmd.Parameters.AddWithValue("@Body", body);
			_cmd.Parameters.AddWithValue("@DueDate", dueDate.HasValue ? (object)dueDate.Value : DBNull.Value);
			_cn.Open();
			int taskId = Convert.ToInt32(_cmd.ExecuteScalar());
			_cn.Close();

			AddTaskFollower(taskId, _currentUserId);

			string message = string.Format("A new task has been created in your list:\n{0}\n\nhttp://teamdoer.com/Tasks/TaskById?taskId={1}", subject, taskId);
			SendMesageToTaskListUsers(taskListId, message, true);

			return taskId;
		}

		public List<TaskInGrid> GetTasksInList(int taskListId)
		{
			List<TaskInGrid> tasks = new List<TaskInGrid>();
			_cmd.Parameters.Clear();
			_cmd.CommandText = @"SELECT TaskID, Subject, Body, IsClosed FROM Tasks WHERE TaskListID=@TaskListID ORDER BY Tasks.IsClosed";
			_cmd.Parameters.AddWithValue("@TaskListID", taskListId);
			_cn.Open();
			SqlDataReader dr = _cmd.ExecuteReader();
			while (dr.Read())
			{
				TaskInGrid task = new TaskInGrid();
				task.TaskId = Convert.ToInt32(dr["TaskID"]);
				task.Subject = dr["Subject"].ToString();
				task.Body = dr["Body"].ToString();
				task.IsClosed = (bool)dr["IsClosed"];
				tasks.Add(task);
			}
			dr.Close();
			_cn.Close();

			return tasks;
		}

		public void GetTaskListInfoFromDB(int taskListId, out string name, out string ownerName)
		{
			name = ownerName = null;

			//getting list name from db
			_cmd.Parameters.Clear();
			_cmd.CommandText = @"SELECT TaskLists.Name, Users.UserName, Users.FirstName, Users.LastName
				FROM TaskLists
				INNER JOIN Users ON Users.UserID = TaskLists.UserID
				WHERE TaskLists.TaskListID=@TaskListID";
			_cmd.Parameters.AddWithValue("@TaskListID", taskListId);
			_cn.Open();
			SqlDataReader dr = _cmd.ExecuteReader();
			if (dr.Read())
			{
				name = dr["Name"].ToString();
				ownerName = Helpers.Utils.GetUserFullName(dr["UserName"], dr["FirstName"], dr["LastName"]);
			}
			_cn.Close();
		}

		public void AddTaskList(string name)
		{
			if (string.IsNullOrWhiteSpace(name)) return;

			_cmd.Parameters.Clear();
			_cmd.CommandText = @"INSERT INTO TaskLists (Name, UserID) VALUES (@Name, @UserID)";
			_cmd.Parameters.AddWithValue("@UserID", _currentUserId);
			_cmd.Parameters.AddWithValue("@Name", name);
			_cn.Open();
			_cmd.ExecuteNonQuery();
			_cn.Close();
		}

		public void DeleteTaskList(int taskListId)
		{
			_cmd.Parameters.Clear();
			_cmd.Parameters.AddWithValue("@TaskListID", taskListId);
			
			_cn.Open();
			_cmd.CommandText = @"DELETE FROM TaskFollowers WHERE TaskID IN (SELECT TaskID FROM Tasks WHERE TaskListID = @TaskListID)";
			_cmd.ExecuteNonQuery();

			_cmd.CommandText = @"DELETE FROM Tasks WHERE TaskListID = @TaskListID";
			_cmd.ExecuteNonQuery();

			_cmd.CommandText = @"DELETE FROM TaskListPermissions WHERE TaskListID = @TaskListID";
			_cmd.ExecuteNonQuery();

			_cmd.CommandText = @"DELETE FROM TaskLists WHERE TaskListID = @TaskListID";
			_cmd.ExecuteNonQuery();
			_cn.Close();
		}

		public List<TaskUser> GetTaskFollowers(int taskId)
		{
			List<TaskUser> list = new List<TaskUser>();

			_cmd.Parameters.Clear();
			_cmd.CommandText = @"SELECT Users.UserName, Users.UserID, Users.FirstName, Users.LastName, Users.Email
				FROM Users INNER JOIN TaskFollowers ON Users.UserID = TaskFollowers.UserID
				WHERE TaskFollowers.TaskID=@TaskID";
			_cmd.Parameters.AddWithValue("@TaskID", taskId);
			_cn.Open();
			SqlDataReader dr = _cmd.ExecuteReader();
			while (dr.Read())
			{
				list.Add(new TaskUser((int)dr["UserID"],
					dr["UserName"].ToString(),
					dr["Email"].ToString(),
					dr["FirstName"].ToString(),
					dr["LastName"].ToString()));
			}
			dr.Close();
			_cn.Close();

			return list;
		}

		public List<TaskUser> GetTaskListSharedUsers(int taskListId)
		{
			List<TaskUser> list = new List<TaskUser>();

			_cmd.Parameters.Clear();
			_cmd.CommandText = @"SELECT Users.UserName, Users.UserID, Users.FirstName, Users.LastName, Users.Email
				FROM Users INNER JOIN TaskListPermissions ON Users.UserID = TaskListPermissions.UserID
				WHERE TaskListPermissions.TaskListID=@TaskListID";
			_cmd.Parameters.AddWithValue("@TaskListID", taskListId);
			_cn.Open();
			SqlDataReader dr = _cmd.ExecuteReader();
			while (dr.Read())
			{
				list.Add(new TaskUser((int)dr["UserID"],
					dr["UserName"].ToString(),
					dr["Email"].ToString(),
					dr["FirstName"].ToString(),
					dr["LastName"].ToString()));
			}
			dr.Close();
			_cn.Close();

			return list;
		}

		public List<TaskList> GetAllTaskLists()
		{
			List<TaskList> taskLists = new List<TaskList>();

			_cmd.Parameters.Clear();
			_cmd.CommandText = @"SELECT TaskListID, Name FROM TaskLists WHERE UserID=@UserID
				OR TaskListID IN (SELECT TaskListID FROM TaskListPermissions WHERE UserID=@UserID)";
			_cmd.Parameters.AddWithValue("@UserID", _currentUserId);
			_cn.Open();
			SqlDataReader dr = _cmd.ExecuteReader();
			while (dr.Read())
			{
				TaskList list = new TaskList();
				list.TaskListId = Convert.ToInt32(dr["TaskListID"]);
				list.Name = dr["Name"].ToString();
				taskLists.Add(list);
			}
			dr.Close();
			_cn.Close();

			return taskLists;
		}

		public Dictionary<string, string> GetUsersForTaskList(int taskListId)
		{
			Dictionary<string, string> users = new Dictionary<string, string>();

			//adding user the list is shared with
			_cmd.Parameters.Clear();
			_cmd.CommandText = @"SELECT Users.UserID, Users.UserName, Users.FirstName, Users.LastName
				FROM Users 
				INNER JOIN TaskListPermissions ON TaskListPermissions.UserID=Users.UserID
				WHERE TaskListPermissions.TaskListID = @TaskListID";
			_cmd.Parameters.AddWithValue("@TaskListID", taskListId);
			_cn.Open();
			SqlDataReader dr = _cmd.ExecuteReader();
			while (dr.Read())
			{
				int userId = (int) dr["UserID"];
				string username = Helpers.Utils.GetUserFullName(dr["UserName"], dr["FirstName"], dr["LastName"]);
				users.Add(userId.ToString(), username);
			}
			dr.Close();

			//now adding the list creator
			_cmd.CommandText = @"SELECT Users.UserID, Users.UserName, Users.FirstName, Users.LastName
				FROM Users 
				INNER JOIN TaskLists ON TaskLists.UserID=Users.UserID
				WHERE TaskLists.TaskListID = @TaskListID";
			dr = _cmd.ExecuteReader();
			if (dr.Read())
			{
				int userId = (int)dr["UserID"];
				if (!users.ContainsKey(userId.ToString()))
				{
					string username = Helpers.Utils.GetUserFullName(dr["UserName"], dr["FirstName"], dr["LastName"]);
					users.Add(userId.ToString(), username);
				}
			}
			dr.Close();
			_cn.Close();

			return users;
		}
	}
}